<!DOCTYPE html>
<html>
<head>
    <title></title>
    <link rel="stylesheet" type="text/css" href="../公用CSS文件/all.css" />
    <script type="text/javascript" src="../js/jquery.min.js"></script> 	<script type="text/javascript" src="../js/js.js"></script>
    <meta charset="utf-8">
    <style type="text/css">
        table{min-width:80%}
    </style>
</head>
<body>

<div class="dt">mysql自定义函数</div>
<ul>
    <div class="ltable showimg">概念和说明</div>
    <div class="img">
        <table>
            <tr>
                <th>函数</th><th>说明</th>
            </tr>
            <tr>
                <td>概念</td><td>命名的独立的代码段，调用时执行特定任务，并可能给调用它的程序返回一个值，Mysql函数必须返回一个值</td>
            </tr>
            <tr>
                <td>作用</td>
                <td>mysql函数一般用来进行一些数据运算</td>
            </tr>
            <tr>
                <td>组成</td><td>函数名、参数、返回格式声明、begin标识、函数体、返回语句、end标识</td>
            </tr>
        </table>
        <li>mysql的函数参数必须声明数据类型、必须有返回值</li>
        <li>mysql函数虽然无法返回结果集，但可以创建临时表存储结果集，临时表在connnect断开后就会销毁，创建临时表和普通表类似，只需要加上temporary标识，如temporary table test</li>
        <li>mysql函数一样从属于库，但信息结构存储在information_schema数据库的routines表中</li>
    </div>

    <div class="ltable showimg">函数声明</div>
    <div class="img">
        <img src="img/函数声明.png">
        <li>整个函数体中只有函数体是需要分句符号的，事实上存储过程也是这样</li>
    </div>

    <div class="ltable showimg">函数相关操作</div>
    <div class="img">
        <table>
            <tr>
                <th>语句</th><th>说明</th>
            </tr>
            <tr>
                <td>create function fucname(参数) 【特性】 begin 函数体 return value ; end ;</td>
                <td>创建自定义函数</td>
            </tr>
            <tr>
                <td>show function status</td><td>查看所有自定义函数</td>
            <tr>
                <td>show function status like '函数名'</td><td>查看符合指定like条件的自定义函数</td>
            </tr>
            <tr>
                <td>drop function 函数名</td><td>删除函数</td>
            </tr>
        </table>
        <li>自定义函数保存在库中，查看时会显示在哪一个数据库</li>
        <li>函数常用的特性只有1个sql  security {definer | invoker}，该子句指明运行时使用的权限，默认definer创建者权限，如果是invoker则使用调用者的权限执行，也可用comment子句设置备注</li>
    </div>

    <div class="ltable showimg">mysql自定义函数和存储过程的区别</div>
    <div class="img">
        <li>存储过程实现的功能要复杂一点。存储过程可以执行包括修改表等一系列数据库操作；用户定义函数仅能进行不返回结果集的CURD操作</li>
        <li>存储过程可以记录集或单一的值，而函数只能返回值。函数只能返回一个变量；而存储过程可以返回多个。存储过程的参数可以有IN,OUT,INOUT三种类型。
        而函数只能有IN类~~存储过程声明时不需要声明返回类型，而函数声明时需要描述返回类型，且函数体中必须包含一个有效的RETURN语句。</li>
        <li>sql语句中可以使用自定义函数，存储过程只能作为独立的语句执行</li>
    </div>

</ul>

<div class="dt">mysql存储过程</div>
<ul>
    <div class="ltable showimg">概念和说明</div>
    <div class="img">
        <li>存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象，其可接受多个返回多个参数，也可以无参数不</li>
        <li>Mysql5.0版本之后才有存储过程这个功能</li>
    </div>

    <div class="ltable showimg">存储过程声明</div>
    <div class="img">
        <img src="img/存储过程.png">
        <li>存储过程可以接受三类参数，in参数代表程序内部会使用它的值，但不会改变；out代表程序会改变它而不使用它的值；inout则既使用它的值也改变它的值</li>
        <li>out、inout参数，调用时必须传入一个变量，这样才能"改变它的值"</li>
    </div>

    <div class="ltable showimg">存储过程相关操作</div>
    <div class="img">
        <table>
            <tr>
                <th>语句</th><th>说明</th>
            </tr>
            <tr>
                <td>create procedure pname(参数) 【特性】 begin 存储过程体 end ;</td>
                <td>创建存储过程</td>
            </tr>
            <tr>
                <td>show procedure status</td><td>查看所有存储过程</td>
            </tr>
            <tr>
                <td>show procedure status like '存储过程名'</td><td>查看符合指定like条件的存储过程</td>
            </tr>
            <tr>
                <td>drop procedure 存储过程名</td><td>删除存储过程</td>
            </tr>
        </table>
        <li>存储过程和函数一样从属于库并存储在information_schema数据库的routines表中</li>
        <li>存储过程常用的特性只有1个sql  security {definer | invoker}，该子句指明运行时使用的权限，默认definer创建者权限，如果是invoker则使用调用者的权限执行，也可用comment子句设置备注</li>
    </div>

    <div class="ltable showimg">mysql自定义函数和存储过程的区别</div>
    <div class="img">
        <li>存储过程实现的功能要复杂一点。存储过程可以执行包括修改表等一系列数据库操作；用户定义函数仅能进行不返回结果集的操作(无法进行select)</li>
        <li>存储过程可以记录集或单一的值，而函数只能返回值。函数只能返回一个变量；而存储过程可以返回多个。存储过程的参数可以有IN,OUT,INOUT三种类型。
        而函数只能有IN类~~存储过程声明时不需要声明返回类型，而函数声明时需要描述返回类型，且函数体中必须包含一个有效的RETURN语句。</li>
        <li>sql语句中可以使用自定义函数，存储过程只能作为独立的语句执行</li>
    </div>
</ul>

<div class="dt">mysql事件调度器</div>
<ul>
    <div class="ltable showimg">概念和说明</div>
    <div class="img">
        <li>事件调度器（Event Scheduler），是MySQL5.1.6起增加的一个非常有特色的功能，主要用作做定时执行某些特定任务（例如：删除记录、对数据进行汇总、数据备份等等）来取代原先只能由操作系统的计划任务来执行的工作任务</li>
        <li>操作系统的计划任务一般只精确到分钟执行，而事件调度器Evenet Scheduler可以精确到秒</li>
    </div>

    <div class="ltable showimg">事件调度器命令</div>
    <div class="img">
        <table>
            <tr>
                <th>命令</th><th>说明</th>
            </tr>
            <tr>
                <td>set global event_scheduler = 1/0 </td><td>开启/关闭事件调度器</td>
            </tr>
            <tr>
                <td>show variables like 'event_scheduler'</td><td>查看事件调度器是否开启</td>
            </tr>
            <tr>
                <td>select @@event_scheduler</td><td>同上</td>
            </tr>
        </table>
        <li>set语句设置的系统参数，为临时参数，在mysql重启或终止后失效</li>
    </div>

    <div class="ltable showimg">创建调度器事件</div>
    <div class="img">
        <img src="img/定时任务.png">
        <li>调度器事件只能执行一个do，也就是单个操作</li>
    </div>

    <div class="ltable showimg">调度器事件指令</div>
    <div class="img">
        <table>
            <tr>
                <th>操作</th><th>命令</th>
            </tr>
            <tr>
                <td>查看调度器所有事件</td>
                <td>show events</td>
            </tr>
            <tr>
                <td>临时关闭事件(重启mysql会自动开启)</td>
                <td>alter event e_test disable</td>
            </tr>
            <tr>
                <td>开启关闭的事件</td>
                <td>alter event e_test ensable</td>
            </tr>
            <tr>
                <td>修改事件</td>
                <td>alter event e_test on schedule every 5 day 【do XX】</td>
            </tr>
            <tr>
                <td>删除事件</td>
                <td>drop schedule e_test</td>
            </tr>
        </table>
    </div>
</ul>

<div class="dt">mysql事务</div>
<ul>
    <div class="ltable showimg">事务的概念</div>
    <div class="img">
        <li>事务Transaction是数据库技术的一种术语，一组命令要么都执行要么都不执行，这样的功能叫做事务</li>
    </div>

    <div class="ltable showimg">事务的特性</div>
    <div class="img">
        <table>
            <tr>
                <th>特性</th><th>说明</th>
            </tr>
            <tr>
                <td>原子性(Atomicity)</td>
                <td>事务的所有操作视为一个原子单位，事务中的操作要么一起回滚要么一起提交</td>
            </tr>
            <tr>
                <td>一致性(Consistently)</td>
                <td>事务必须使数据库从一个一致性状态变更为另一个一致性状态</td>
            </tr>
            <tr>
                <td>隔离性(Isolation)</td>
                <td>一个事务的操作必须与另一个事务的操作相隔离</td>
            </tr>
            <tr>
                <td>持久性(Durability)</td>
                <td>事务所做操作对数据的影响是持久的，即使系统重启或出现故障仍可以恢复</td>
            </tr>
        </table>
        <li>数据库的事务隔离一般通过锁机制实现</li>
        <li>mysql支持事务的存储引擎有Innodb、bdb等，基本上使用的都是Innodb</li>
        <li>Innodb操作事务的机制是通过两种日志完成的：redo日志和undo日志。redo日志记录运行日志存储在mysql安装目录data/ib_logfileN中，
        undo日志记录事务前的数据和表数据内容一同保存在.idb文件中</li>
    </div>

    <div class="ltable showimg">事务操作语句</div>
    <div class="img">
        <table>
            <tr>
                <th>操作</th><th>说明</th>
            </tr>
            <tr>
                <td>开启事务</td>
                <td>begin、start transaction</td>
            <tr>
                <td>提交事务</td>
                <td>commit</td>
            </tr>
            <tr>
                <td>回滚事务</td>
                <td>rollback</td>
            </tr>
            <tr>
                <td>设置事务隔离级别</td>
                <td>set session transaction isolation level 隔离级别</td>
            </tr>
        </table>
    </div>

    <div class="ltable showimg">事务隔离级别</div>
    <div class="img">
        <table>
            <tr>
                <th>隔离级别</th><th>说明</th><th>隐患</th><th>MVCC机制</th>
            </tr>
            <tr>
                <td>读未提交(read uncommited)</td>
                <td>其他事务的操作未提交，本事务就能读取其修改的数据</td>
                <td>脏读、幻读、不可重复读</td>
                <td>排他锁在执行完本次操作就释放</td>
            </tr>
            <tr>
                <td>读已提交(read commited)</td>
                <td>仅能读取其他事务修改后的数据</td>
                <td>幻读、不可重复读</td>
                <td>排他锁在事务完成后才释放</td>
            </tr>
            <tr>
                <td>可重复读(repeatable-read)</td>
                <td>保证事务中的可重复读性</td>
                <td>幻读</td>
                <td>共享锁在事务提交后才释放</td>
            </tr>
            <tr>
                <td>串行化(serializable)</td>
                <td>保证事务不会发生脏读、不可重复读、幻读</td>
                <td>无</td>
                <td>所有锁都在提交后释放，且加锁的表无法插入新数据</td>
            </tr>
        </table>
        <li>脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据</li>
        <li>幻读：事务A更新了表的数据，事务B的操作导致应该在事务A的更新操作中的数据没有按预期的更新，好像发生了幻觉，这就是幻读</li>
        <li>不可重复读：事务A查询了两次数据，事务B在事务A两次查询的间隙更新了数据，导致事务A两次读取的数据中出现了非自己操作导致的数据不一致，这就是不可重复读</li>
    </div>

    <div class="ltable showimg">锁机制</div>
    <div class="img">
        <table>
            <tr>
                <th>锁类型</th><th>说明</th>
            </tr>
            <tr>
                <td>共享锁</td>
                <td>读锁，获取读锁的事务能进行读操作，同时阻止别的事务进行写操作</td>
            </tr>
            <tr>
                <td>排他锁</td>
                <td>写锁，获取写锁的事务能进行写操作，同时阻止别的事务进行读、写操作，也就是无法获取共享和排他锁</td>
            </tr>
            <tr>
                <td>意向锁</td>
                <td>锁粒度为表的锁，意义是表达一种意向，分意向共享锁和意向排他锁</td>
            </tr>
        </table>
        <li>Innodb的锁机制虽然是行锁，但是当不确定操作哪行数据时(全表扫描)时，也会执行表锁锁机制</li>
    </div>
</ul>

<div class="dt">mysql编程</div>
<ul>
    <div class="ltable showimg">mysql变量</div>
    <div class="img">
        <table>
            <tr>
                <th>操作</th><th>示范</th><th>说明</th>
            </tr>
            <tr>
                <td>声明局部变量</td>
                <td>declare 变量名 数据类型</td>
                <td></td>
            </tr>
            <tr>
                <td>赋值局部变量</td>
                <td>set 变量名 = 值</td>
            </tr>
            <tr>
                <td>赋值/声明全局变量</td>
                <td>set @变量名 = 值</td>
                <td></td>
            </tr>
            <tr>
                <td>赋值/声明系统变量</td>
                <td>set @@变量名 = 值</td>
                <td>声明新的系统变量需要supper权限，root用户都不行(怎么获取不知道)</td>
            </tr>
            <tr>
                <td>赋值/声明会话变量</td>
                <td>set session variablename = 值</td>
                <td>会话变量其实是系统变量的一种</td>
            </tr>
        </table>
        <li>局部变量的作用域是在begin和end的区间内，类似很多语言的花括号</li>
        <li>系统变量代表的是系统配置的参数，按理说应该server一个周期内有效，但是测试结果居然是重连就失效了</li>
        <li>所有变量都能使用select查看，系统变量还可以使用show variables like "变量名"查询</li>
    </div>

    <div class="ltable showimg">游标cursor</div>
    <div class="img">
        <table>
            <tr>
                <th>操作</th><th>语法</th><th>说明</th>
            </tr>
            <tr>
                <td>创建游标</td>
                <td>declare 游标名 cursor for 查询语句</td>
                <td></td>
            </tr>
            <tr>
                <td>打开游标</td>
                <td>open cursor</td>
                <td></td>
            </tr>
            <tr>
                <td>使用游标</td>
                <td>fetch 游标名 into var1,var2...</td>
                <td>使用游标的本质是将游标的值存进变量里</td>
            </tr>
            <tr>
                <td>关闭游标</td>
                <td>close 游标名</td>
                <td></td>
            </tr>
        </table>
        <li>游标的使用过程中，open和close会遍历游标的值</li>
        <li>游标可以使用在函数体和存储过程中</li>
    </div>

    <div class="ltable showimg">流程控制</div>
    <div class="img">
        <table>
            <tr>
                <th>流程控制语句</th>
                <th>使用语法</th>
                <th>说明</th>
            </tr>
            <tr>
                <td>if else</td>
                <td>if condition then code; 【elseif condition then code;...】【else then code;】 end if;</td>
                <td></td>
            </tr>
            <tr>
                <td>case</td>
                <td>case 值 when value then code; 【when value then code;...】 【else code;】 end;</td>
                <td></td>
            </tr>
            <tr>
                <td>loop循环</td>
                <td>loopname:loop if condition then code; end if; end loop;</td>
                <td>loop循环内部可以使用leave loopname退出循环</td>
            </tr>
            <tr>
                <td>while循环</td>
                <td>while condition  do code end while;</td>
                <td></td>
            </tr>
        </table>
    </div>

    <div class="ltable showimg">handler处理</div>
    <div class="img">
        <table>
            <tr>
                <th>操作</th><th>说明</th>
            </tr>
            <tr>
                <td>创建handler会话</td>
                <td>handler 表 open 【as 别名】</td>
            </tr>
            <tr>
                <td>读取handler数据</td>
                <td>handler table read first、next、prev 【where条件】</td>
            </tr>
            <tr>
                <td>关闭handler会话</td>
                <td>handler table close</td>
            </tr>
        </table>
        <li>handler一次read仅读取一行数据</li>
        <li>handler其实是一种类似长连接的方式打开一张表，并在handler中存储当前读取记录的指针，每次带where条件的读取都会重新定位指针，first、next等关键词会移动指针</li>
        <td>在用where定位了指针之后，指针指向的是数据的记录，就算数据更改成不符合where，也不影响接下来的read next读取到它</td>
    </div>

</ul>

<div class="dt">mysql性能优化</div>
<ul>
    <div class="ltable showimg">数据库设计</div>
    <div class="img">
        <table>
            <tr>
                <th>优化项</th><th>说明</th>
            </tr>
            <tr>
                <td>数据表优化</td>
                <td>遵循数据库设计三原则设计表结构</td>
            </tr>
            <tr>
                <td>字段类型优化</td>
                <td>选择合适的字段类型，尽量使用int类型，优先使用查询性能更好、字节占用更少的数据类型</td>
            </tr>
            <tr>
                <td>字段约束优化</td>
                <td>int类型不存储负数时使用unsigned，可空字段最好为not null并设置默认值因为会有更好的查询性能</td>
            </tr>
            <tr>
                <td>索引优化</td>
                <td>经常用来做检索where条件和group分组的字段建立索引，并依据场景和存储引擎选用合适的索引类型(unique index严格来说本质上是加了唯一约束的b+tree)</td>
            </tr>
        </table>
    </div>

    <div class="ltable showimg">sql优化</div>
    <div class="img">
        <table>
            <tr>
                <th>优化项</th>
                <th>说明</th>
            </tr>
            <tr>
                <td>条件排序</td>
                <td>将预期查询结果更少的数据排列在前面</td>
            </tr>
            <tr>
                <td>条件优化</td>
                <td>条件中尽可能少的使用函数、运算等对字段处理，避免使用like "%value"查询，这样都会影响索引的使用</td>
            </tr>
            <tr>
                <td>事务和性能取舍</td>
                <td>对于完整性要求不高的但性能要求高的数据，可以不使用事务</td>
            </tr>
            <tr>
                <td>多表查询优化</td>
                <td>尽量少join表，尽量少使用非等值的any、all子查询，这些语句性能开销巨大</td>
            </tr>
        </table>
    </div>

    <div class="ltable showimg">配置优化</div>
    <div class="img">
        <table>
            <tr>
                <th>优化项</th><th>说明</th>
            </tr>
            <tr>
                <td>事务、锁机制优化</td>
                <td>对于准确性要求不高的数据可以调低事务隔离级别，对于数据量大的系统可以调大事务日志占用空间配置</td>
            </tr>
            <tr>
                <td>缓冲、缓存优化</td>
                <td>如果整个数据库系统整体高访问低写入，可以适当调低缓存区大小，其他一些场景也可以调整合适的配置</td>
            </tr>
        </table>
    </div>

    <div class="ltable showimg">分布式集群系统</div>
    <div class="img">
        <table>
            <tr>
                <th>搭配方式</th><th>说明</th><th>缺陷</th>
            </tr>
            <tr>
                <td>读写分离</td>
                <td>从从库读取数据，往主库写入数据，定期同步数据</td>
                <td>读取延迟，有脏数据</td>
            </tr>
        </table>
    </div>

</ul>

</body>
</html>